from tkinter import *
import numpy as np
import pymysql
import time
import matplotlib.pyplot as plt
from matplotlib.backends.backend_tkagg import FigureCanvasTkAgg


plt.rcParams['font.sans-serif'] =['Microsoft YaHei']
plt.rcParams['axes.unicode_minus'] = False


#  其他年份
def in_tb():
    global zc_s1, zc_s2, zc_s3, zc_s4, zc_s5, zc_s6, zc_s7, zc_s8, zc_s9, zc_s10, zc_s11, zc_s12
    global sr_s1, sr_s2, sr_s3, sr_s4, sr_s5, sr_s6, sr_s7, sr_s8, sr_s9, sr_s10, sr_s11, sr_s12
    zc_s1 = 0;zc_s2 = 0;zc_s3 = 0;zc_s4 = 0;zc_s5 = 0;zc_s6 = 0;zc_s7 = 0;zc_s8 = 0;zc_s9 = 0;zc_s10 = 0;zc_s11 = 0;zc_s12 = 0;
    sr_s1 = 0;sr_s2 = 0;sr_s3 = 0;sr_s4 = 0;sr_s5 = 0;sr_s6 = 0;sr_s7 = 0;sr_s8 = 0;sr_s9 = 0;sr_s10 = 0;sr_s11 = 0;sr_s12 = 0;
    db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True',
                         charset='utf8')
    cursor = db.cursor()
    sql = 'select * from pay'
    cursor.execute(sql)
    data = cursor.fetchall()  # 获取所有的数据
    stime = sz_tb1.get()
    for i in data:
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 1:
            zc_s1 = zc_s1 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 2:
            zc_s2 = zc_s2 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 3:
            zc_s3 = zc_s3 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 4:
            zc_s4 = zc_s4 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 5:
            zc_s5 = zc_s5 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 6:
            zc_s6 = zc_s6 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 7:
            zc_s7 = zc_s7 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 8:
            zc_s8 = zc_s8 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 9:
            zc_s9 = zc_s9 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 10:
            zc_s10 = zc_s10 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 11:
            zc_s11 = zc_s11 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 12:
            zc_s12 = zc_s12 + int(i[1]);
    db.close()
    db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True',
                         charset='utf8')
    cursor = db.cursor()
    sql = 'select * from income'
    cursor.execute(sql)
    data = cursor.fetchall()  # 获取所有的数据
    for i in data:
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 1:
            sr_s1 = sr_s1 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 2:
            sr_s2 = sr_s2 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 3:
            sr_s3 = sr_s3 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 4:
            sr_s4 = sr_s4 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 5:
            sr_s5 = sr_s5 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 6:
            sr_s6 = sr_s6 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 7:
            sr_s7 = sr_s7 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 8:
            sr_s8 = sr_s8 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 9:
            sr_s9 = sr_s9 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 10:
            sr_s10 = sr_s10 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 11:
            sr_s11 = sr_s11 + int(i[1]);
        if int(stime) == int(i[3][0:4]) and int(i[3][5:7]) == 12:
            sr_s12 = sr_s12 + int(i[1]);
    db.close()


# 显示当前年数据
def local_tb():
    global zc_s1, zc_s2, zc_s3, zc_s4, zc_s5, zc_s6, zc_s7, zc_s8, zc_s9, zc_s10, zc_s11, zc_s12
    global sr_s1, sr_s2, sr_s3, sr_s4, sr_s5, sr_s6, sr_s7, sr_s8, sr_s9, sr_s10, sr_s11, sr_s12
    zc_s1=0;zc_s2=0;zc_s3=0;zc_s4=0;zc_s5=0;zc_s6=0;zc_s7=0;zc_s8=0;zc_s9=0;zc_s10=0;zc_s11=0;zc_s12=0;
    sr_s1 = 0;sr_s2 = 0;sr_s3 = 0;sr_s4 = 0;sr_s5 = 0;sr_s6 = 0;sr_s7 = 0;sr_s8 = 0;sr_s9 = 0;sr_s10 = 0;sr_s11 = 0;sr_s12 = 0;
    curr_time = time.localtime()
    time_str = time.strftime("%Y", curr_time);
    db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True',
                         charset='utf8')
    cursor = db.cursor()
    sql = 'select * from pay'
    cursor.execute(sql)
    data = cursor.fetchall()  # 获取所有的数据
    for i in data:
        if int(time_str)==int(i[3][0:4]) and int(i[3][5:7])==1:
            zc_s1=zc_s1+int(i[1]);
        if int(time_str)==int(i[3][0:4]) and int(i[3][5:7])==2:
            zc_s2=zc_s2+int(i[1]);
        if int(time_str)==int(i[3][0:4]) and int(i[3][5:7])==3:
            zc_s3=zc_s3+int(i[1]);
        if int(time_str)==int(i[3][0:4]) and int(i[3][5:7])==4:
            zc_s4=zc_s4+int(i[1]);
        if int(time_str)==int(i[3][0:4]) and int(i[3][5:7])==5:
            zc_s5=zc_s5+int(i[1]);
        if int(time_str)==int(i[3][0:4]) and int(i[3][5:7])==6:
            zc_s6=zc_s6+int(i[1]);
        if int(time_str)==int(i[3][0:4]) and int(i[3][5:7])==7:
            zc_s7=zc_s7+int(i[1]);
        if int(time_str)==int(i[3][0:4]) and int(i[3][5:7])==8:
            zc_s8=zc_s8+int(i[1]);
        if int(time_str)==int(i[3][0:4]) and int(i[3][5:7])==9:
            zc_s9=zc_s9+int(i[1]);
        if int(time_str)==int(i[3][0:4]) and int(i[3][5:7])==10:
            zc_s10=zc_s10+int(i[1]);
        if int(time_str)==int(i[3][0:4]) and int(i[3][5:7])==11:
            zc_s11=zc_s11+int(i[1]);
        if int(time_str)==int(i[3][0:4]) and int(i[3][5:7])==12:
            zc_s12=zc_s12+int(i[1]);
    db.close()
    db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True',
                         charset='utf8')
    cursor = db.cursor()
    sql = 'select * from income'
    cursor.execute(sql)
    data = cursor.fetchall()  # 获取所有的数据
    for i in data:
        if int(time_str) == int(i[3][0:4]) and int(i[3][5:7]) == 1:
            sr_s1 = sr_s1 + int(i[1]);
        if int(time_str) == int(i[3][0:4]) and int(i[3][5:7]) == 2:
            sr_s2 = sr_s2 + int(i[1]);
        if int(time_str) == int(i[3][0:4]) and int(i[3][5:7]) == 3:
            sr_s3 = sr_s3 + int(i[1]);
        if int(time_str) == int(i[3][0:4]) and int(i[3][5:7]) == 4:
            sr_s4 = sr_s4 + int(i[1]);
        if int(time_str) == int(i[3][0:4]) and int(i[3][5:7]) == 5:
            sr_s5 = sr_s5 + int(i[1]);
        if int(time_str) == int(i[3][0:4]) and int(i[3][5:7]) == 6:
            sr_s6 = sr_s6 + int(i[1]);
        if int(time_str) == int(i[3][0:4]) and int(i[3][5:7]) == 7:
            sr_s7 = sr_s7 + int(i[1]);
        if int(time_str) == int(i[3][0:4]) and int(i[3][5:7]) == 8:
            sr_s8 = sr_s8 + int(i[1]);
        if int(time_str) == int(i[3][0:4]) and int(i[3][5:7]) == 9:
            sr_s9 = sr_s9 + int(i[1]);
        if int(time_str) == int(i[3][0:4]) and int(i[3][5:7]) == 10:
            sr_s10 = sr_s10 +int(i[1]);
        if int(time_str) == int(i[3][0:4]) and int(i[3][5:7]) == 11:
            sr_s11 = sr_s11 +int(i[1]);
        if int(time_str) == int(i[3][0:4]) and int(i[3][5:7]) == 12:
            sr_s12 = sr_s12 +int(i[1]);
    db.close()

def create_matplotlib(frame):
        global zc_s1, zc_s2, zc_s3, zc_s4, zc_s5, zc_s6, zc_s7, zc_s8, zc_s9, zc_s10, zc_s11, zc_s12
        global sr_s1, sr_s2, sr_s3, sr_s4, sr_s5, sr_s6, sr_s7, sr_s8, sr_s9, sr_s10, sr_s11, sr_s12
        bar_width = 0.4
        # 创建绘图对象f
        f = plt.figure(num=2, figsize=(12, 7), dpi=80, facecolor="lavenderblush", edgecolor='green', frameon=True)
        # 创建一副子图
        fig1 = f.add_subplot(1, 1, 1)
        x = ["1月", "2月", "3月", "4月", "5月", "6月","7月","8月","9月","10月","11月","12月" ]
        y = []
        if sz_tb1.get() == "":
            local_tb()
        else:
            in_tb()
        y1_zc = [zc_s1, zc_s2, zc_s3, zc_s4, zc_s5, zc_s6,zc_s7, zc_s8, zc_s9, zc_s10, zc_s11, zc_s12]
        y2_sr = [sr_s1, sr_s2, sr_s3, sr_s4, sr_s5, sr_s6,sr_s7, sr_s8, sr_s9, sr_s10, sr_s11, sr_s12]
        # 计算
        sr=str(sr_s1+sr_s2+sr_s3+sr_s4+sr_s5+sr_s6+sr_s7+sr_s8+sr_s9+sr_s10+sr_s11+sr_s12)
        sz_in.set(sr)
        zc = str(zc_s1 + zc_s2 + zc_s3 + zc_s4 + zc_s5 + zc_s6 + zc_s7 + zc_s8 + zc_s9 + zc_s10 + zc_s11 + zc_s12)
        sz_out.set(zc)
        if int(sr) > int(zc):
            ce=str(int(sr)-int(zc))
            sz_ce.set(ce)
        else:
            ce = str(int(zc) - int(sr))
            sz_ce.set(ce)
        plt.bar(np.arange(12), y1_zc,label = '支出', color='steelblue', alpha=0.8, width=bar_width)
        plt.bar(np.arange(12) + bar_width,y2_sr, label = '收入',color='indianred', alpha=0.8, width=bar_width)
        plt.xticks(np.arange(12) + bar_width, x)
        plt.yticks(y)

        # 为每个条形图添加数值标签
        for xx, yy in enumerate(y1_zc):
            plt.text(xx-0.35, yy, '%s' % yy)

        for xx1, yy1 in enumerate(y2_sr):
            plt.text(xx1+0.2, yy1, '%s' % yy1)
        # 字体
        font1 = {
                 'size': 20,
                 }
        fig1.set_xlabel('月份',font1)  # 确定坐标轴标题
        fig1.set_ylabel("金额(元)",font1)
        fig1.legend()
        # 把绘制的图形显示到tkinter窗口上
        canvas = FigureCanvasTkAgg(f, master=frame)
        canvas.get_tk_widget().place(x=20,y=150)
        canvas.draw()

def readtwo():
    create_matplotlib(window_sztb)


def sz_tb():
    global sz_tb1,sz_tb,window_sztb,sz_in,sz_out,sz_ce
    window_sztb = Tk()
    window_sztb.title("收支统计")
    # 固定窗口位置
    width = 1090
    height = 900
    screenwidth = window_sztb.winfo_screenwidth()
    screenheight = window_sztb.winfo_screenheight()
    alignstr = '%dx%d+%d+%d' % (width, height, (screenwidth - width) / 2, (screenheight - height) / 5)
    window_sztb.geometry(alignstr)
    # 设置窗口大小不可改变
    window_sztb.resizable(width=False, height=False)
    # 设置窗口颜色
    window_sztb.configure(background='ivory')


    Label(window_sztb, text="收支统计", font=('Arial', 22), fg="darkorange").place(x=20, y=10)
    Label(window_sztb, text="收支统计图表:", font=('Arial', 16),fg="green").place(x=20, y=100)

    sz_tb= StringVar(window_sztb, value="")
    sz_tb1 = Entry(window_sztb, textvariable=sz_tb, font=('Arial', 16), width=6, fg="pink", bg="azure")
    sz_tb1.place(x=160, y=101)
    Label(window_sztb, text="年", font=('Arial', 16), fg="green").place(x=236, y=100)


    # 收支总计
    Label(window_sztb, text="总收入:", font=('Arial', 16), fg="green").place(x=20, y=740)
    Label(window_sztb, text="总支出:", font=('Arial', 16), fg="green").place(x=20, y=790)

    sz_in = StringVar(window_sztb, value="")
    sz_in1 = Entry(window_sztb, textvariable=sz_in, font=('Arial', 16), width=9, fg="pink", bg="azure")
    sz_in1.place(x=110, y=740)

    sz_out = StringVar(window_sztb, value="")
    sz_out1 = Entry(window_sztb, textvariable=sz_out, font=('Arial', 16), width=9, fg="pink", bg="azure")
    sz_out1.place(x=110, y=790)

    Label(window_sztb, text="元", font=('Arial', 16), fg="green").place(x=225, y=740)
    Label(window_sztb, text="元", font=('Arial', 16), fg="green").place(x=225, y=790)

    # 差额
    Label(window_sztb, text="差   额:", font=('Arial', 16), fg="green").place(x=20, y=840)
    Label(window_sztb, text="元", font=('Arial', 16), fg="green").place(x=225, y=840)

    sz_ce = StringVar(window_sztb, value="")
    sz_ce1 = Entry(window_sztb, textvariable=sz_ce, font=('Arial', 16), width=9, fg="pink", bg="azure")
    sz_ce1.place(x=110, y=840)


    btn1=Button(
        window_sztb,
        text="显示",  # 文本
        bg='limegreen',  # 背景颜色
        fg='snow',  # 文本颜色
        relief='raised',  # 边框的3D样式
        bd=2,  # 边框的大小
        height=1,  # 高度
        width=4,  # 宽度
        padx=1,  # 内间距，字体与边框的X距离
        pady=1,  # 内间距，字体与边框的Y距离
        cursor='arrow',
        font=('Yu Gothic Medium', 9),  # 字体
        command=readtwo)
    btn1.place(x=270, y=100)
    create_matplotlib(window_sztb)
    window_sztb.mainloop()
